package com.blockchain.console.service.system.achieve;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.codec.digest.UnixCrypt;

import com.blockchain.common.enums.IsPass;
import com.blockchain.common.enums.SysAccountStatus;
import com.blockchain.console.service.system.SysUserManage;
import com.blockchain.console.service.system.entity.SysUser;
import com.blockchain.console.service.system.query.SysUserQuery;
import com.blockchain.framework.http.session.SessionManager;
import com.blockchain.framework.service.ServiceFactory;
import com.blockchain.framework.service.ServiceResource;
import com.blockchain.framework.service.exception.LogicalException;
import com.blockchain.framework.service.exception.ParameterException;
import com.blockchain.framework.service.query.ArrayParser;
import com.blockchain.framework.service.query.ItemParser;
import com.blockchain.framework.service.query.Paging;
import com.blockchain.framework.service.query.PagingResult;
import com.blockchain.util.StringHelper;
import com.blockchain.util.parser.EnumParser;

public class SysUserManageImpl extends AbstractSystemService implements
		SysUserManage {

	public static class SysUserManageFactory implements
			ServiceFactory<SysUserManage> {

		@Override
		public SysUserManage newInstance(ServiceResource serviceResource) {
			return new SysUserManageImpl(serviceResource);
		}
	}

	public SysUserManageImpl(ServiceResource serviceResource) {
		super(serviceResource);
	}

	@Override
	public int add(String accountName, String password, String name,
			String status,String sjh) throws Throwable {
		serviceResource.openTransactions();
		String select = "SELECT F01 FROM T7011 WHERE F02=?";
		try (Connection connection = getConnection()) {
			try (PreparedStatement ps = connection.prepareStatement(select)) {
				ps.setString(1, accountName);
				try (ResultSet rs = ps.executeQuery()) {
					if (rs.next()) {
						throw new LogicalException("登录账户已经存在.");
					}
				}
			}
		}
		String sql = "INSERT INTO T7011 SET F02=?,F03=?,F04=?,F05=?,F06=?,F09=?,F10=?";
		password = UnixCrypt.crypt(password, DigestUtils.sha256Hex(password));
		int sysUserId = insert(getConnection(), sql, accountName, password,
				name, status, new Timestamp(System.currentTimeMillis()),
				IsPass.S,sjh);
		return sysUserId;
	}

	@Override
	public SysUser get(int id) throws Throwable {
		if (id <= 0) {
			throw new ParameterException("指定的系统账号ID不存在.");
		}
		String schema = serviceResource.getSystemDefine().getSchemaName(
				SessionManager.class);
		String sql = "SELECT T7011.F01,T7011.F02,T7011.F03,T7011.F04,T7011.F05,T7011.F06,T7011.F07,T7011.F08,_1020.F01 AS ROLEID,T7011.F10 sjh FROM T7011";
		StringBuilder sb = new StringBuilder();
		sb.append(sql);
		sb.append(" INNER JOIN " + schema
				+ "._1022 ON T7011.F01=_1022.F01 INNER JOIN " + schema
				+ "._1020 ON _1022.F02=_1020.F01 WHERE T7011.F01=?");
		return select(getConnection(), new ItemParser<SysUser>() {

			@Override
			public SysUser parse(ResultSet resultSet) throws SQLException {
				SysUser sysUser = new SysUser();
				if (resultSet.next()) {
					sysUser.id = resultSet.getInt(1);
					sysUser.accountName = resultSet.getString(2);
					sysUser.password = resultSet.getString(3);
					sysUser.name = resultSet.getString(4);
					sysUser.status = EnumParser.parse(SysAccountStatus.class,
							resultSet.getString(5));
					sysUser.createTime = resultSet.getTimestamp(6);
					sysUser.lastTime = resultSet.getTimestamp(7);
					sysUser.lastIp = resultSet.getString(8);
					sysUser.roleId = resultSet.getInt(9);
					sysUser.sjh=resultSet.getString(10);
				}
				return sysUser;
			}
		}, sb.toString(), id);
	}

	@Override
	public void update(int id, String password, String name, String status,String sjh)
			throws Throwable {
		serviceResource.openTransactions();
		String sql = "UPDATE T7011 SET F03=?,F04=?,F05=?,F10=? WHERE F01=?";
		String sql2 = "UPDATE T7011 SET F04=?,F05=?,F10=? WHERE F01=?";
		try (Connection connection = getConnection()) {
			try (PreparedStatement ps = connection
					.prepareStatement("SELECT F01 FROM T7011 WHERE F01=?")) {
				ps.setInt(1, id);
				try (ResultSet rs = ps.executeQuery()) {
					if (!rs.next()) {
						throw new LogicalException("指定的用户ID不存在.");
					}
				}
			}
		}
		if (StringHelper.isEmpty(password)) {
			execute(getConnection(), sql2, name, status, sjh,id);
		} else {
			password = UnixCrypt.crypt(password,
					DigestUtils.sha256Hex(password));
			execute(getConnection(), sql, password, name, status,sjh, id);
		}

	}

	@Override
	public PagingResult<SysUser> serarch(SysUserQuery query, Paging paging)
			throws Throwable {
		String schema = serviceResource.getSystemDefine().getSchemaName(
				SessionManager.class);
		String sql = "SELECT T7011.F01,T7011.F02,T7011.F04,T7011.F05,T7011.F06,T7011.F07,T7011.F08,_1020.F02 AS ROLENAME,T7011.F10 SJH FROM T7011";
		StringBuilder sb = new StringBuilder();
		sb.append(sql);
		sb.append(" INNER JOIN " + schema
				+ "._1022 ON T7011.F01=_1022.F01 INNER JOIN " + schema
				+ "._1020 ON _1022.F02=_1020.F01 WHERE 1=1");
		List<Object> parameters = new ArrayList<>();
		if (query != null) {
			String accountName = query.getAccountName();
			String name = query.getName();
			SysAccountStatus status = query.getStatus();
			Timestamp startTime = query.getCreateTimeStart();
			Timestamp endTime = query.getCreateTimeEnd();
			if (!StringHelper.isEmpty(accountName)) {
				sb.append(" AND T7011.F02 LIKE ?");
				parameters
						.add(getSQLConnectionProvider().allMatch(accountName));
			}
			if (!StringHelper.isEmpty(name)) {
				sb.append(" AND T7011.F04 LIKE ?");
				parameters.add(getSQLConnectionProvider().allMatch(name));
			}
			if (status != null) {
				sb.append(" AND T7011.F05=?");
				parameters.add(status);
			}
			if (startTime != null) {
				sb.append(" AND DATE(T7011.F07)>=?");
				parameters.add(startTime);
			}
			if (endTime != null) {
				sb.append(" AND DATE(T7011.F07)<=?");
				parameters.add(endTime);
			}
			if (query.getRoleId() > 0) {
				sb.append(" AND _1020.F01=?");
				parameters.add(query.getRoleId());
			}
		}
		sb.append(" ORDER BY T7011.F06 DESC");
		return selectPaging(getConnection(), new ArrayParser<SysUser>() {

			@Override
			public SysUser[] parse(ResultSet rs) throws SQLException {
				List<SysUser> sysUsers = new ArrayList<>();
				while (rs.next()) {
					SysUser sysUser = new SysUser();
					sysUser.id = rs.getInt(1);
					sysUser.accountName = rs.getString(2);
					sysUser.name = rs.getString(3);
					sysUser.status = EnumParser.parse(SysAccountStatus.class,
							rs.getString(4));
					sysUser.createTime = rs.getTimestamp(5);
					sysUser.lastTime = rs.getTimestamp(6);
					sysUser.lastIp = rs.getString(7);
					sysUser.roleName = rs.getString(8);
					sysUser.sjh = rs.getString(9);
					sysUsers.add(sysUser);
				}
				return sysUsers.toArray(new SysUser[sysUsers.size()]);
			}
		}, paging, sb.toString(), parameters);
	}
}
